﻿// ***********************************************************************
// Assembly         : ELS
// Author           : linhvn
// Created          : 10-20-2012
//
// Last Modified By : linhvn
// Last Modified On : 10-22-2012
// ***********************************************************************
// <copyright file="UserDataAccess.cs" company="">
//     Copyright (c) . All rights reserved.
// </copyright>
// <summary></summary>
// ***********************************************************************
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace ELS.User
{
    /// <summary>
    /// Class UserDataAccess
    /// </summary>
    public class UserDataAccess
    {
        /// <summary>
        /// The connection string
        /// </summary>
        string connectionString = UserCommon.connectionString;

        /// <summary>
        /// Checks the exist user by username.
        /// </summary>
        /// <param name="username">The username.</param>
        /// <returns>DataTable.</returns>
        internal DataTable CheckExistUserByUsername(string username)
        {
            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select * from [User] where [User].USERNAME = @username", sqlConnection);
                    cmd.Parameters.Add(new SqlParameter("username", SqlDbType.NVarChar)).Value = username;
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dt);
                }
                catch (SqlException e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
            return dt;
        }

        /// <summary>
        /// Selects all information from the user by username.
        /// </summary>
        /// <param name="username">The username.</param>
        /// <returns>DataTable.</returns>
        internal DataTable GetUserInfoByUsername(string username)
        {
            DataTable dt = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {                
                try
                {
                    sqlConnection.Open();

                    SqlCommand cmd = new SqlCommand(@"Select [User].ID as USER_USERID,
	                                                        [User].USERNAME,
	                                                        [User].PASSWORD,
	                                                        [User].FULLNAME,
	                                                        [User].EMAIL,
	                                                        [User].ADDRESS,
	                                                        [User].PHONE,
	                                                        [User].ORGANIZATION,
	                                                        UserGroup.ID as USERGROUP_ID,
	                                                        UserGroup.USER_ID as USERGROUP_USERID,
	                                                        UserGroup.GROUP_ID as USERGROUP_GROUPID,
	                                                        [Group].ID as GROUP_ID,
	                                                        [Group].NAME as GROUPNAME,
	                                                        [Group].DESCRIPTION as GROUP_DESCRIPTION
                                                            from [User],UserGroup,[Group] 
                                                            where [User].ID = UserGroup.USER_ID
                                                            and UserGroup.GROUP_ID = [Group].ID
                                                            and [User].USERNAME =  @username ", sqlConnection);
                    cmd.Parameters.Add(new SqlParameter("@username", SqlDbType.NVarChar)).Value = username;                    
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dt);                            
                }
                catch (SqlException e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }        
            }
            return dt;
        }

        /// <summary>
        /// Get all user by paging.
        /// </summary>
        /// <returns>DataTable.</returns>
        internal DataTable GetAllUsersPaging(int mintPageSize, int intSkip)
        {
            DataTable dtAllUser = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select TOP " + mintPageSize + " * from [User] WHERE [User].[ID]  NOT IN " 
                        + "(SELECT TOP " + intSkip + " [ID] from [USER] order by [USERNAME]) order by [USER].[USERNAME]", sqlConnection);
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtAllUser);
                    //dtAllUser.DefaultView.Sort = "USERNAME ASC";
                    return dtAllUser;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// Get all user by searching keyword and paging condition.
        /// </summary>
        /// <returns>DataTable.</returns>
        internal DataTable GetAllUsersByKeywordPaging(string keyword, int mintPageSize, int intSkip)
        {
            DataTable dtUser = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select TOP " + mintPageSize + " * from [User] WHERE [User].[USERNAME] like '%" + keyword + "%' AND [User].[ID]  NOT IN "
                        + "(SELECT TOP " + intSkip + " [ID] from [USER] WHERE [USERNAME] like '%" + keyword + "%' order by [USERNAME] ASC) order by [User].[USERNAME] ASC", sqlConnection);
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtUser);
                    //dtUser.DefaultView.Sort = "USERNAME ASC";
                    return dtUser;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int CountAllUsers()
        {
            int intCount = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    DataTable dtTable = new DataTable();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = @"SELECT COUNT (*) as TOTAL FROM [User]";
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlComm);
                    dtAdapter.Fill(dtTable);
                    intCount = (int)dtTable.Rows[0]["TOTAL"];
                    return intCount;
                }
                catch (SqlException e)
                {
                    return intCount;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int CountAllUsersByKeyword(string keyword)
        {
            int intCount = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    DataTable dtTable = new DataTable();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = @"SELECT COUNT (*) as TOTAL FROM [User] where [User].[USERNAME] like '%" + keyword + "%'";
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(sqlComm);
                    dtAdapter.Fill(dtTable);
                    intCount = (int)dtTable.Rows[0]["TOTAL"];
                    return intCount;
                }
                catch (SqlException e)
                {
                    return intCount;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// Get the user info.
        /// </summary>
        /// <returns>DataTable.</returns>
        internal DataTable GetUserInfo()
        {
            DataTable dtAllUser = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select USERNAME, FULLNAME, EMAIL, ADDRESS, PHONE, ORGANIZATION from [User]", sqlConnection);
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtAllUser);
                    return dtAllUser;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// Inserts the user.
        /// </summary>
        /// <param name="newUser">The new user.</param>
        /// <returns>System.Int32.</returns>
        internal int InsertUser(User newUser)
        {
            int rowInserted = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = @"INSERT INTO [User] (
                                                                
                                                                [User].USERNAME,
                                                                [User].PASSWORD,
                                                                [User].FULLNAME,
                                                                [User].EMAIL,
                                                                [User].ADDRESS,
                                                                [User].PHONE,
                                                                [User].ORGANIZATION
                                            ) VALUES (
                                                                
                                                                @username,
                                                                @password,
                                                                @fullname,
                                                                @email,
                                                                @address,
                                                                @phone,
                                                                @organization
                                                        );
                                        SELECT @@IDENTITY AS 'Identity' ";
                    //sqlComm.Parameters.Add("@id", SqlDbType.NVarChar).Value = newUser.UserId;
                    sqlComm.Parameters.Add("@username", SqlDbType.NVarChar).Value = newUser.Username;
                    sqlComm.Parameters.Add("@password", SqlDbType.NVarChar).Value = newUser.Password;
                    sqlComm.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = newUser.Fullname;
                    sqlComm.Parameters.Add("@email", SqlDbType.NVarChar).Value = newUser.Email;
                    sqlComm.Parameters.Add("@address", SqlDbType.NVarChar).Value = newUser.Address;
                    sqlComm.Parameters.Add("@phone", SqlDbType.NVarChar).Value = newUser.Phone;
                    sqlComm.Parameters.Add("@organization", SqlDbType.NVarChar).Value = newUser.Organization;
                    sqlComm.ExecuteNonQuery();
                                        
                    for(int GroupCount=0; GroupCount < newUser.Group.Count; GroupCount++)
                    {
                        SqlCommand sqlComm1 = new SqlCommand();
                        sqlComm1 = sqlConnection.CreateCommand();
                        sqlComm1.CommandText = @"INSERT INTO UserGroup (
                                                                    
                                                                    UserGroup.USER_ID,
                                                                    UserGroup.GROUP_ID
                                                  ) VALUES (
                                                                    
                                                                    (select ID from [User] 
                                                                        where ID = (select MAX(ID)from [User])),
                                                                    @groupid
                                                             )";

                        sqlComm1.Parameters.Add("@groupid", SqlDbType.Int).Value = newUser.Group[GroupCount];
                        rowInserted = sqlComm1.ExecuteNonQuery();
                    }
                    return rowInserted;

                }
                catch (SqlException e)
                {
                    return rowInserted;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// Updates the user's info by admin.
        /// </summary>
        /// <param name="user">The user.</param>
        /// <returns>System.Int32.</returns>
        internal int UpdateUser(User user)
        {
            int rowUpdate = -1;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand sqlComm = new SqlCommand();
                    sqlComm = sqlConnection.CreateCommand();
                    sqlComm.CommandText = @"UPDATE [User] SET   
                                                                [User].PASSWORD = @password,
                                                                [User].FULLNAME = @fullname,
                                                                [User].EMAIL = @email,
                                                                [User].ADDRESS = @address,
                                                                [User].PHONE = @phone,
                                                                [User].ORGANIZATION = @organization
                                             WHERE 
                                                                [User].USERNAME = @username                                                                
                                          ";
                    sqlComm.Parameters.Add("@username", SqlDbType.NVarChar).Value = user.Username;
                    sqlComm.Parameters.Add("@password", SqlDbType.NVarChar).Value = user.Password;
                    sqlComm.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = user.Fullname;
                    sqlComm.Parameters.Add("@email", SqlDbType.NVarChar).Value = user.Email;
                    sqlComm.Parameters.Add("@address", SqlDbType.NVarChar).Value = user.Address;
                    sqlComm.Parameters.Add("@phone", SqlDbType.NVarChar).Value = user.Phone;
                    sqlComm.Parameters.Add("@organization", SqlDbType.NVarChar).Value = user.Organization;
                    rowUpdate = sqlComm.ExecuteNonQuery();

                    SqlCommand sqlComm1 = new SqlCommand();
                    sqlComm1 = sqlConnection.CreateCommand();
                    sqlComm1.CommandText = @"DELETE FROM UserGroup                                                                 
                                             WHERE 
                                                Usergroup.USER_ID = @userId";

                    sqlComm1.Parameters.Add("@userId", SqlDbType.NVarChar).Value = SelectUserIdByUsername(user.Username); ;                    
                    rowUpdate = sqlComm1.ExecuteNonQuery();
                    
                    for (int GroupCount = 0; GroupCount < user.Group.Count; GroupCount++)
                    {
                        SqlCommand sqlComm2 = new SqlCommand();
                        sqlComm2 = sqlConnection.CreateCommand();
                        sqlComm2.CommandText = @"INSERT INTO UserGroup (
                                                                    
                                                                    UserGroup.USER_ID,
                                                                    UserGroup.GROUP_ID
                                                  ) VALUES (
                                                                   
                                                                    @id,
                                                                    @groupid
                                                             )";

                        
                        
                        string userid = SelectUserIdByUsername(user.Username);

                        //sqlComm2.Parameters.Add("@usergroupid", SqlDbType.NVarChar).Value = usergroupid;
                        sqlComm2.Parameters.Add("@groupid", SqlDbType.Int).Value = user.Group[GroupCount];
                        sqlComm2.Parameters.Add("@id", SqlDbType.Int).Value = userid;

                        rowUpdate = sqlComm2.ExecuteNonQuery();
                        
                    }
                    return rowUpdate;
                }
                catch (Exception e)
                {
                    return rowUpdate;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// Selects the user id by username.
        /// </summary>
        /// <param name="username">The username.</param>
        /// <returns>System.String.</returns>
        private string SelectUserIdByUsername(string username)
        {
            string userid = String.Empty;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select [User].ID from [User] where [User].USERNAME = @username ", sqlConnection);
                    cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = username;
                    userid = cmd.ExecuteScalar().ToString();
                    return userid;
                }
                catch (Exception e)
                {
                    return userid;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// Selects all Group.
        /// </summary>
        /// <returns>DataTable.</returns>
        internal DataTable SelectAllGroup()
        {
            DataTable dtGroup = new DataTable();
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select * from [Group] ", sqlConnection);                    
                    SqlDataAdapter dtAdapter = new SqlDataAdapter(cmd);
                    dtAdapter.Fill(dtGroup);
                    return dtGroup;
                }
                catch (Exception e)
                {
                    return null;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// Selects the fullname by username.
        /// </summary>
        /// <param name="username">The username.</param>
        /// <returns>System.String.</returns>
        internal string SelectFullnameByUsername(string username)
        {
            string fullname = String.Empty;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"Select [User].FULLNAME from [User] where [User].USERNAME = @username ", sqlConnection);
                    cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = username;
                    fullname = cmd.ExecuteScalar().ToString();
                    return fullname;
                }
                catch (Exception e)
                {
                    return fullname;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        /// <summary>
        /// Deletes the user by admin.
        /// </summary>
        /// <param name="list">The list.</param>
        /// <returns>System.Int32.</returns>
        internal int DeleteUser(List<string> list)
        {
            int rowUpdate = 0;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    for (int countUser = 0; countUser < list.Count; countUser++)
                    {
                        SqlCommand sqlComm1 = new SqlCommand();
                        sqlComm1 = sqlConnection.CreateCommand();
                        sqlComm1.CommandText = @"Delete from UserGroup 
                                                where UserGroup.USER_ID = ( select [User].ID from [User] 
                                                                            where [User].USERNAME = @username)";
                        sqlComm1.Parameters.Add("@username", SqlDbType.NVarChar).Value = list[countUser];                        
                        sqlComm1.ExecuteNonQuery();

                        SqlCommand sqlComm2 = new SqlCommand();
                        sqlComm2 = sqlConnection.CreateCommand();
                        sqlComm2.CommandText = @"Delete from [User] where [User].USERNAME = @username";
                        sqlComm2.Parameters.Add("@username", SqlDbType.NVarChar).Value = list[countUser];                        
                        rowUpdate += sqlComm2.ExecuteNonQuery();
                    }
                    return rowUpdate;
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    return rowUpdate;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal int DeleteUsingUserGrpInListByGroupId(string listGroup)
        {
            int rowDelete = -1;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"DELETE FROM UserGroup WHERE UserGroup.GROUP_ID IN (" + listGroup + ")", sqlConnection);
                    rowDelete = cmd.ExecuteNonQuery();
                    return rowDelete;
                }
                catch (Exception e)
                {
                    return rowDelete;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }

        internal DataTable GetGroupIdByUsername(string username)
        {
            DataTable groupIdTable = new DataTable(); ;
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
                try
                {
                    sqlConnection.Open();
                    SqlCommand cmd = new SqlCommand(@"SELECT UserGroup.GROUP_ID FROM UserGroup, [User]
                                                        WHERE UserGroup.USER_ID = [User].ID
                                                        and [User].USERNAME = @username ", sqlConnection);
                    cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = username;
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(groupIdTable);
                    return groupIdTable;
                }
                catch (Exception e)
                {
                    return groupIdTable;
                }
                finally
                {
                    sqlConnection.Close();
                }
            }
        }                
    }
}
